import pymysql
import random

sql_conf = {
    "host": "localhost",
    "user": "root",
    "password": "123456",
    "db": "test"
}


con_local = pymysql.connect(**sql_conf)
cursor_local = con_local.cursor()

test = []


def insert_one():
    for i in range(20):
        SQL = f"""insert into test(name) values ('it"s ok')"""
        print(SQL)
        cursor_local.execute(SQL)
        con_local.commit()


def insert_many():
    SQL = """insert into test(name) values (%s)"""
    cursor_local.executemany(SQL, [(f"name_{i}",) for i in range(15, 30)])
    con_local.commit()


def get_students():
    SQL = """select * from test"""
    cursor_local.execute(SQL)
    query_set = cursor_local.fetchall()
    for item in query_set:
        print(item)


if __name__ == "__main__":
    insert_many()






#创建
#单条插入
#INSERT INTO test (name) values ("bbb")
#多条插入
#INSERT INTO test (name) values ("bbb"),("vvv"),("qqq")

#检索
#条件查询（关键字：where,and，or ）
#select * from test where name="bbb" and id=6
#模糊匹配（关键字：like,%）
#select * from test where name like"%jh"
#限制返回的记录数（关键字:limit）
#只能对正式返回结果用limit
#select * from test where name like"%jh" limit 1
#过滤重复值(关键字：distinct)
#select distinct id, name  from test where name like"%jh"
#获取总数（关键字：count）
#select count(*)from test
#排序
#升序ASC（默认排序方法）
#select * from test order by id,name asc;
#降序DESC
#select * from test order by id,name desc;


#集合操作
#并集UNION
#子语句的select必须拥有相同数量的列，且列的数据类型也相同
#去重
# (select name from test where name is not null)
# union
# (select name from test2 where name is not null)
#不去重
# (select name from test where name is not null)
# union all
# (select name from test2 where name is not null)

#交集（Join/inner join）
# select * from
# (select name from test where name is not null) t1
# join
# (select name from test2 where name is not null) t2
# on t1.name = t2.name


#差集
#A对B的差集
# select t1.name as name_1,t2.name as name_2 from
# (select name from test where name is not null) t1
# left join
# (select name from test2 where name is not null) t2
# on t1.name = t2.name
# where t2.name is null

#B对A的差集
# select t1.name as name_1,t2.name as name_2 from
# (select name from test where name is not null) t1
# right join
# (select name from test2 where name is not null) t2
# on t1.name = t2.name
# where t1.name is null


#补集
#A和B的补集=A对B的差集+B对A的差集
# select t1.name as name_1,t2.name as name_2 from
# (select name from test where name is not null) t1
# right join
# (select name from test2 where name is not null) t2
# on t1.name = t2.name
# where t1.name is null
#
# union
#
# select t1.name as name_1,t2.name as name_2 from
# (select name from test where name is not null) t1
# left join
# (select name from test2 where name is not null) t2
# on t1.name = t2.name
# where t2.name is null

#更新（update）
# update test set name='ljh5' where name ='ljh'

#删除（delete）
# delete from test  where name ='ljh5'

#truncate 表名（删除全部记录，保留表结构）